/*
	select distinct ps_type,ps_type_name from process_step
	select * from location
	--and lth.timestamp between '2007-09-01 00:00:00.000' and '2007-09-30 11:59:50.000'

	exec sp_production_report_proc '0','0','09/01/2007','09/30/2007','0'
*/
Alter proc sp_production_report_proc

@PS_TYPE 	VARCHAR(50),
@VENDOR_ID	VARCHAR(50),
@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@DEFAULT_PROMPT	VARCHAR(50)

AS
BEGIN
	DECLARE @SQLSTRING	VARCHAR(4000)
	
	DECLARE @TEMP_VARCHAR VARCHAR(50)
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME
	
	SET @TEMP_VARCHAR = CONVERT(VARCHAR, @FROMDATE, 101)+' 12:00AM'
	SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	SET @TEMP_VARCHAR = NULL
	SET @TEMP_VARCHAR = CONVERT(VARCHAR, @TODATE, 101)+' 11:59PM'
	SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

	SET @SQLSTRING = N'
	SELECT 
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
		) AS [Base Product],PART_NUMBER [Part Number],
		LT.FAB_LOT_NUMBER as [Fab Lot No], LT.LOT_NUMBER [Lot No], L_Vendor_Name [Vendor],PS_TYPE [Process],
		MIN(LTH.DATE_IN) [Date In],MAX(LTH.WAFER_QTY_IN) [Wafer Qty], MAX(LTH.QTY_IN) [Qty In],	LT.LOT_CATEGORY [Lot Category]
	FROM 
		LOT_TABLE_HISTORY LTH, Lot_Table LT, PRODUCT P,PROCESS_STEP PS,LOCATION L
	WHERE 
		LTH.Lot_Id = LT.Lot_ID
		AND LTH.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LTH.LOCATION_ID = L.LOCATION_ID
		AND LT.Lot_Current_Status = ''Close''
		AND Lt.Date_In < '''+ CONVERT(VARCHAR,@END_DATE)+'''
		AND PS.PS_Type_Name in ( ''Fab'',''Sort'', ''Assembly'', ''Test'', ''Class Store'' )
		AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''  
		+ ' AND LT.Lot_Id NOT IN ( SELECT LOT_ID FROM LOT_TABLE_HISTORY LTH1 WHERE LTH1.LOT_ID = LT.Lot_Id  AND LTH1.TIMESTAMP > '''+ CONVERT(VARCHAR,@END_DATE)+''' )'
				
	IF @VENDOR_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND VENDOR_ID = '''+ @VENDOR_ID +''''
	END

	IF @PS_TYPE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND PS_TYPE = '''+ @PS_TYPE +''''
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( PS_TYPE_NAME = ''FAB'' OR PS_TYPE_NAME =''Sort'' OR PS_TYPE_NAME = ''ASSEMBLY'' OR PS_TYPE_NAME = ''TEST'' ) '
	END
	
	SET @SQLSTRING = @SQLSTRING + ' GROUP BY LT.FAB_LOT_NUMBER, LT.LOT_NUMBER, L_Vendor_Name, PS_TYPE, LT.LOT_CATEGORY, PART_NUMBER,P.Product_ID,P.P_Parent_Product_ID
	ORDER BY [Base Product],[Part Number],(CASE
			WHEN PS_TYPE = ''FAB'' THEN 1
			WHEN PS_TYPE = ''FAB OPTION'' THEN 2
			WHEN PS_TYPE = ''SORT'' THEN 3
			WHEN PS_TYPE = ''ASSEMBLY'' THEN 4
			WHEN PS_TYPE = ''FT'' THEN 5
			WHEN PS_TYPE = ''FPO'' THEN 6
			WHEN PS_TYPE = ''Tape & Reel'' THEN 7
			ELSE NULL
		END),[Fab Lot No] '

	EXEC (@SQLSTRING)
END


